package com.augurit.gzcgwsp.webservice.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import org.springside.modules.orm.Page;

/**
 * jdbc连接方式
 * @author 张军
 *
 */
@Repository
public class ExChangeDataDao{
        private static final Logger log = Logger.getLogger(ExChangeDataDao.class); 
        private static String url = null; 
        private static String username = null; 
        private static String password = null;  

        static { 
                try { 
                	Locale locale = Locale.getDefault(); 
            		ResourceBundle localResource = ResourceBundle.getBundle("application", locale); 
            		url  = localResource.getString("jdbc.url");; 
                    username =localResource.getString("jdbc.username"); 
                    password =localResource.getString("jdbc.password"); 
                } catch (Exception e) { 
                        log.error("系统加载application.properties配置文件异常！", e); 
                } 
                //注册驱动类 
                try { 
                       Class.forName("oracle.jdbc.driver.OracleDriver");
                } catch (ClassNotFoundException e) { 
                        log.error("加载数据库驱动异常！", e); 
                } 
        } 

        /** 
         * 创建数据库连接 
         * 
         * @return 数据库连接 
         */ 
        public static Connection getConnection() { 
                Connection conn = null; 
                //创建数据库连接 
                try { 
                        conn = DriverManager.getConnection(url, username, password); 
                } catch (SQLException e) { 
                        log.error("创建数据库连接发生异常，请检查！", e); 
                } 
                return conn; 
        } 

        /**
         * 获取Statement
         * @param conn
         * @return
         * @throws Exception
         */
    	public static  Statement getStatement(Connection conn) throws Exception {
    		Statement t = conn.createStatement();
    		return t;
    	}


    	/**
    	 * 查询数据返回集合
    	 * @param sql
    	 * @return
    	 */
    	public static  List queryForList(String sql) {
    		Connection conn = null;
    		Statement st = null;
    		ResultSet rs = null;
    		List<Map> tempList = new ArrayList<Map>();
    		try {
    			conn = getConnection();
    			st = getStatement(conn);
    			rs = st.executeQuery(sql);
    			ResultSetMetaData meta = rs.getMetaData();
    			int columnCount = meta.getColumnCount();
    			while(rs.next()) {
    				Map<String, String> map = new LinkedHashMap<String, String>();
    				for(int i=1; i<=columnCount; i++) {
    					String colName = meta.getColumnLabel(i).toLowerCase();
    					String colValue = rs.getString(i);
    					map.put(colName, colValue);
    				}
    				tempList.add(map);
    			}
    			
    		}catch(Exception e) {
    			log.error("查找异常", e);
    		}finally {
    			close(conn, st, rs);
    		}
    		
    		return tempList;
    	}
        
    	
    	/**
    	 * 分页查询
    	 * @param page
    	 * @param map
    	 * @return
    	 */
    	public List searchByPage(String sql,Page page, Map map) {
    		StringBuffer sb=new StringBuffer();
    	    sb.append("  select * from ("+"\r");
    	    sb.append("  select  *   from ("+"\r");
    	    sb.append(" select  a.*,rownum num from ("+"\r");
    	    sb.append(sql+") a )"+"\r");
    	    sb.append(" where   rownum <="+page.getPageSize()*page.getPageNo()+")"+"\r");
    	    sb.append(" where   num> ="+((page.getPageNo()-1)*page.getPageSize()+1)+"\r");
    		List list= this.queryForList(sb.toString());
    		return list;
    	}
    	
    	/**
    	 * 查询sql的数量
    	 * @param sql
    	 * @return
    	 */
    	public Long getTotalCount(String sql){
    		String sizesql=" select count(*) as countnum from ( " + sql+" )";
    		List list=this.queryForList(sizesql);
    		Map countMap=(Map) list.get(0);
    		String total=(String) countMap.get("countnum");
    		if(StringUtils.isNotBlank(total)){
    			return Long.valueOf(total);
    		}else{
    			return 0L;
    		}
    	}
    	
    	
    	public Page findPage(String sql,Page page,Map map) {
    		Long total=this.getTotalCount(sql);
    		List datas=searchByPage(sql, page, map);
    		page.setTotalCount(total);
    		page.setResult(datas);
    		return page;
    	}
    	
    	
    	
        
    	/**  
    	 * 关闭连接资源  
    	 * @param conn  
    	 */
    	public static void close(Connection conn, Statement st, ResultSet rs) {
    		if(rs != null) {
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				log.error("关闭ResultSet发生异常，请检查！", e); 
    			}
    		}
    		if(st != null) {
    			try {
    				st.close();
    			} catch (SQLException e) {
    				log.error("关闭Statement发生异常，请检查！", e); 
    			}
    		}
    		if(conn != null) {
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				log.error("关闭数据库连接发生异常，请检查！", e); 
    			}
    		}
    	}

}


